package com.s2bdatautil;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author Hongten
 * @created 2014-5-20
 */
public class ReadExcel {
    

    /**
     * Read the Excel 2010
     * @param path the path of the excel file
     * @return
     * @throws IOException
     */
    public static List<List<List<String>>> readXlsx(String path) throws IOException {
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        List<List<List<String>>> list = new ArrayList<List<List<String>>>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
        	List<List<String>> onesheet = new ArrayList<List<String>>();
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                	List<String> data = new ArrayList<String>();
                	for(int k=0;k<xssfRow.getLastCellNum();k++) {
                		data.add(getValue(xssfRow.getCell(k)));
                	}
                	onesheet.add(data);
                }
            }
            list.add(onesheet);
        }
        return list;
    }

    /**
     * Read the Excel 2003-2007
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
    public static List<List<List<String>>> readXls(String path) throws IOException {
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        List<List<List<String>>> list = new ArrayList<List<List<String>>>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            List<List<String>> onesheet = new ArrayList<List<String>>();
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                	List<String> data = new ArrayList<String>();
                	for(int k=0;k<hssfRow.getLastCellNum();k++) {
                		data.add(getValue(hssfRow.getCell(k)));
                	}
                	onesheet.add(data);
                }
            }
            list.add(onesheet);
        }
        return list;
    }

    @SuppressWarnings("static-access")
    public static String getValue(XSSFCell xssfRow) {
    	if(xssfRow == null)
    		return null;
        return String.valueOf(xssfRow.getStringCellValue());
    }

    @SuppressWarnings("static-access")
    public static String getValue(HSSFCell hssfCell) {
    	return String.valueOf(hssfCell.getStringCellValue());
    }
}